Course: MySQL Training
Topic: Counting Records Using COUNT()
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
The COUNT() function is an aggregate function in MySQL used to count records or values in a database query result. It is one of the most frequently used functions in database management, reporting, and data analysis because it helps users understand how many records exist in a table or how many records satisfy a particular condition.
For example, a company may want to know how many employees work in its organization, how many customers have registered on its website, how many orders were placed during a month, or how many products are available in inventory. The COUNT() function can answer these questions directly through SQL queries.
COUNT() can count all rows, count non-NULL values in a specific column, or count unique values. It can also be combined with clauses such as WHERE, GROUP BY, HAVING, and ORDER BY to generate more detailed reports.
The three commonly used forms are:
COUNT(*) — counts all rows in the selected result.COUNT(column_name) — counts rows where the specified expression is not NULL.COUNT(DISTINCT column_name) — counts unique non-NULL values in a column.After completing this topic, learners should be able to:
COUNT() is a MySQL aggregate function that returns the number of rows or non-NULL expression values, depending on the form used. The result is a numeric count.
For example, suppose a students table contains five records. The following query counts the rows returned by the table:
SELECT COUNT(*) AS total_students
FROM students;If the table contains five rows, the result will be 5. The alias total_students gives the result column a meaningful name.
To learn COUNT(), create a sample employees table. We will use this table throughout the examples.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
email VARCHAR(100),
salary DECIMAL(10,2),
city VARCHAR(50)
);The table contains an employee ID, employee name, department, email address, salary, and city.
INSERT INTO employees
(employee_id, employee_name, department, email, salary, city)
VALUES
(1, 'Aarav', 'IT', '[email protected]', 50000, 'Mumbai'),
(2, 'Priya', 'HR', '[email protected]', 40000, 'Pune'),
(3, 'Rohan', 'IT', NULL, 60000, 'Mumbai'),
(4, 'Neha', 'Finance', '[email protected]', 55000, 'Delhi'),
(5, 'Kabir', 'HR', NULL, 35000, 'Pune'),
(6, 'Meera', 'Finance', '[email protected]', 65000, 'Mumbai'),
(7, 'Ishaan', 'IT', '[email protected]', 45000, 'Delhi'),
(8, 'Ananya', 'Sales', NULL, 48000, 'Pune');SELECT *
FROM employees;The table contains eight employees. Three employees have NULL email values, which will help demonstrate the difference between counting rows and counting non-NULL values.
COUNT(*) counts every row returned by the query, regardless of whether any column contains NULL. It is the most direct option when you want to know how many records are included in a result.
SELECT COUNT(*)
FROM table_name;SELECT COUNT(*) AS total_employees
FROM employees;Expected result: 8
The query counts all eight rows in the employees table.
SELECT COUNT(*) AS total_customers
FROM customers;This query counts all records in a customers table, assuming that the table exists in the database.
SELECT COUNT(*) AS total_products
FROM products;The result indicates how many product records are returned by the query.
Suppose an employee has no email address recorded. The employee still exists as a row in the table. COUNT(*) includes that employee in the total count.
SELECT COUNT(*) AS total_records
FROM employees;Even though three email values are NULL, the result remains 8 because COUNT(*) counts rows rather than requiring every column to contain a value.
COUNT(column_name) counts the number of rows where the specified expression evaluates to a non-NULL value. Rows where that expression is NULL are excluded from the count.
SELECT COUNT(column_name)
FROM table_name;SELECT COUNT(email) AS employees_with_email
FROM employees;Expected result: 5
Five employees have an email address recorded. The three employees whose email values are NULL are not included in COUNT(email).
SELECT COUNT(salary) AS employees_with_salary
FROM employees;Expected result: 8
All eight salary values are non-NULL, so the function counts all eight employees.
SELECT COUNT(department) AS employees_with_department
FROM employees;This query counts rows with a non-NULL department value. If a department value is NULL, that row is excluded from this count.
COUNT(column_name) excludes NULL values but does not automatically exclude empty strings or zero values.
SELECT
COUNT(*) AS all_rows,
COUNT(email) AS non_null_emails
FROM employees;If an email column contains an empty string such as '', that row is counted by COUNT(email), because an empty string is not NULL.
If empty strings should be excluded as well, use a condition such as:
SELECT COUNT(*) AS valid_email_rows
FROM employees
WHERE email IS NOT NULL
AND TRIM(email) <> '';This query excludes NULL email values and values containing only ordinary leading or trailing spaces after trimming.
COUNT(DISTINCT column_name) counts unique non-NULL values in a column. Duplicate values are counted only once.
This form is useful when you need to count unique departments, cities, categories, customer IDs, or other distinct values rather than every record.
SELECT COUNT(DISTINCT column_name)
FROM table_name;SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;Expected result: 4
The departments in the sample table are IT, HR, Finance, and Sales. Even though several employees belong to the same department, each department is counted once.
SELECT COUNT(DISTINCT city) AS unique_cities
FROM employees;Expected result: 3
The distinct cities are Mumbai, Pune, and Delhi.
SELECT COUNT(DISTINCT email) AS unique_email_addresses
FROM employees;This query counts unique non-NULL email values. NULL values are not counted, and repeated email values count only once.
COUNT(DISTINCT column_name) excludes NULL values. If every value in the column is NULL, the result is 0.
SELECT COUNT(DISTINCT email) AS unique_emails
FROM employees;Only distinct, non-NULL email addresses contribute to the result.
| Feature | COUNT(*) | COUNT(column) | COUNT(DISTINCT column) |
|---|---|---|---|
| Purpose | Counts rows. | Counts non-NULL expression values. | Counts unique non-NULL expression values. |
| Includes rows with NULL in the selected column? | Yes. | No, if the expression evaluates to NULL. | No, NULL is excluded. |
| Counts duplicate values separately? | Yes, every row is counted. | Yes, every non-NULL occurrence is counted. | No, duplicate values count once. |
| Example | COUNT(*) |
COUNT(email) |
COUNT(DISTINCT city) |
| Common use | Total number of records. | Number of records with available values. | Number of unique values. |
SELECT
COUNT(*) AS total_employees,
COUNT(email) AS employees_with_email,
COUNT(DISTINCT department) AS unique_departments
FROM employees;Expected results: total_employees = 8, employees_with_email = 5, and unique_departments = 4.
The WHERE clause filters individual rows before COUNT() calculates the result. This allows you to count only records that satisfy specific conditions.
SELECT COUNT(*) AS it_employee_count
FROM employees
WHERE department = 'IT';Expected result: 3
SELECT COUNT(*) AS mumbai_employee_count
FROM employees
WHERE city = 'Mumbai';Expected result: 3
SELECT COUNT(*) AS high_salary_employees
FROM employees
WHERE salary > 45000;This query counts only employees whose salary is greater than 45000.
SELECT COUNT(*) AS employees_without_email
FROM employees
WHERE email IS NULL;Expected result: 3
Use IS NULL to check for missing values. A condition such as email = NULL is not the correct way to test whether a column is NULL.
SELECT COUNT(*) AS employees_with_email
FROM employees
WHERE email IS NOT NULL;Expected result: 5
This result matches COUNT(email) for the full table in this example. However, the two expressions communicate different query intentions: one filters rows by a condition, while the other counts non-NULL values in an expression.
SELECT COUNT(*) AS matching_employees
FROM employees
WHERE department = 'IT'
AND salary > 45000;This query counts IT employees whose salary is greater than 45000.
GROUP BY organizes rows with the same value in a column or set of columns into groups. COUNT() can then calculate a separate count for each group.
For example, instead of counting every employee in the company as one total, you can count the number of employees in each department.
SELECT
grouping_column,
COUNT(*) AS record_count
FROM table_name
GROUP BY grouping_column;SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;The result contains one row for each department and its corresponding employee count.
SELECT
city,
COUNT(*) AS employee_count
FROM employees
GROUP BY city;This query calculates the number of employees in Mumbai, Pune, and Delhi separately.
SELECT
department,
city,
COUNT(*) AS employee_count
FROM employees
GROUP BY department, city
ORDER BY department, city;Grouping by two columns produces a separate count for each department-and-city combination. For example, IT employees in Mumbai form a different group from IT employees in Delhi.
SELECT
department,
COUNT(email) AS employees_with_email
FROM employees
GROUP BY department;This query counts only employees with non-NULL email values within each department.
HAVING filters groups after GROUP BY has created them and COUNT() has calculated their values. It is useful when you need to display only categories that meet a minimum or maximum count.
SELECT
grouping_column,
COUNT(*) AS record_count
FROM table_name
GROUP BY grouping_column
HAVING COUNT(*) > condition_value;SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;This query displays only departments containing more than one employee.
SELECT
city,
COUNT(*) AS employee_count
FROM employees
GROUP BY city
HAVING COUNT(*) >= 2;This query returns cities that have at least two employee records.
| WHERE | HAVING |
|---|---|
| Filters individual rows before grouping. | Filters groups after aggregation. |
| Used to select records that match conditions. | Used to select groups that meet aggregate conditions. |
Example: WHERE city = 'Mumbai' |
Example: HAVING COUNT(*) > 2 |
ORDER BY sorts the results of a query. When used with COUNT(), it helps rank categories according to the number of records they contain.
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;DESC sorts departments from the largest employee count to the smallest. Use ASC to sort from the smallest count to the largest.
SELECT
city,
COUNT(*) AS employee_count
FROM employees
GROUP BY city
ORDER BY employee_count DESC
LIMIT 2;LIMIT 2 returns only the first two rows after sorting. This is useful when creating reports showing the most common locations or categories.
Sometimes the requirement is to count unique combinations of two or more columns rather than unique values in a single column.
SELECT COUNT(DISTINCT department, city) AS unique_department_city_pairs
FROM employees;In MySQL, the multiple-expression form of COUNT(DISTINCT) counts distinct combinations for which the supplied expressions are non-NULL. Rows containing NULL in either expression are excluded from this count.
If you need to count every distinct department-and-city combination, including combinations containing NULL values, group the rows first and count the resulting groups.
SELECT COUNT(*) AS total_combinations
FROM (
SELECT department, city
FROM employees
GROUP BY department, city
) AS grouped_values;This approach counts each group produced by GROUP BY, including groups with NULL grouping values.
Conditional counting allows you to count rows that meet different conditions in a single query. It is especially useful for reports and dashboards.
SELECT
COUNT(*) AS total_employees,
COUNT(CASE WHEN department = 'IT' THEN 1 END) AS it_employees,
COUNT(CASE WHEN department = 'HR' THEN 1 END) AS hr_employees,
COUNT(CASE WHEN department = 'Finance' THEN 1 END) AS finance_employees
FROM employees;Each CASE expression returns 1 for matching rows and NULL for nonmatching rows. COUNT() counts only the non-NULL results, producing a count for each department.
SELECT
COUNT(CASE WHEN salary >= 50000 THEN 1 END) AS salary_50000_or_more,
COUNT(CASE WHEN salary < 50000 THEN 1 END) AS salary_below_50000
FROM employees;This query returns two counts in a single row.
SELECT
COUNT(*) AS total_employees,
COUNT(email) AS with_email,
COUNT(CASE WHEN email IS NULL THEN 1 END) AS without_email
FROM employees;This report shows the total number of employees, the number with recorded email addresses, and the number without an email address.
COUNT() is frequently used with JOIN operations to count related records across multiple tables. For example, a customer may have several orders, and a report may need to count the orders placed by each customer.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10,2),
order_status VARCHAR(30)
);INSERT INTO orders
(order_id, customer_id, order_amount, order_status)
VALUES
(101, 1, 2500, 'Completed'),
(102, 1, 1500, 'Pending'),
(103, 2, 3000, 'Completed'),
(104, 3, 1800, 'Cancelled'),
(105, 3, 2200, 'Completed');These examples assume a customers table with a customer_id column that identifies each customer.
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;COUNT(o.order_id) counts matching orders. Because order_id is a primary key and cannot be NULL in an actual order record, it is suitable for this purpose.
With a LEFT JOIN, a customer with no matching order still produces one result row, with NULL values in the order columns. COUNT(*) counts that row, so it can return 1 for a customer with no orders. COUNT(o.order_id) returns 0 because the unmatched order_id is NULL.
Therefore, when counting matching records on the right side of a LEFT JOIN, count a non-NULL column from that table, usually its primary key.
Understanding NULL is essential for accurate counting. A NULL value represents missing or unknown information, and COUNT() treats NULL differently depending on the expression.
SELECT COUNT(*) AS all_employee_rows
FROM employees;This query counts every employee row, including employees whose email is NULL.
SELECT COUNT(email) AS non_null_emails
FROM employees;This query excludes employees whose email value is NULL.
SELECT COUNT(*) AS null_email_count
FROM employees
WHERE email IS NULL;This query counts rows where the email column is NULL.
SELECT COUNT(*) AS recorded_emails
FROM employees
WHERE email IS NOT NULL;This query counts employee records whose email column contains a non-NULL value.
An empty string is not the same as NULL. Therefore, COUNT(email) includes rows whose email is an empty string, provided the expression itself is not NULL.
SELECT COUNT(*) AS empty_email_count
FROM employees
WHERE email = '';This query counts email values that are exactly empty strings.
SELECT COUNT(*) AS non_empty_email_count
FROM employees
WHERE email IS NOT NULL
AND TRIM(email) <> '';This query excludes both NULL and empty or ordinary space-only values. It is useful when checking whether a text field contains a meaningful value.
A subquery can be used when the result of one query needs to be counted by another query. This is useful for counting grouped results or filtering data based on a calculated set.
SELECT COUNT(*) AS total_departments
FROM (
SELECT department
FROM employees
GROUP BY department
) AS department_list;This query counts the number of department groups returned by the subquery. If department can be NULL, GROUP BY creates a separate NULL group, and that group is included in this count.
SELECT COUNT(*) AS above_average_employees
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);The subquery calculates the average salary. The outer query counts employees whose salaries are greater than that average.
COUNT() is straightforward to use, but performance can matter when working with large tables.
For exact counting requirements, use COUNT() with the appropriate filters. An approximate row count may be sufficient for some administrative tasks, but it should not be substituted for an exact count when accuracy is important.
| Mistake | Why It Is Incorrect | Correct Approach |
|---|---|---|
| Using COUNT(column) to count every row | Rows where the expression is NULL are excluded. | Use COUNT(*) when every row must be counted. |
| Using COUNT(*) to count available emails | Rows with NULL emails are included. | Use COUNT(email) or filter with WHERE email IS NOT NULL. |
| Using email = NULL | Ordinary equality comparisons do not correctly test for NULL. | Use email IS NULL. |
| Forgetting DISTINCT | Duplicate values are counted repeatedly. | Use COUNT(DISTINCT column) for unique non-NULL values. |
| Using COUNT(*) after a LEFT JOIN to count matches | Unmatched left-side rows still appear in the result. | Count a non-NULL right-side key, such as COUNT(o.order_id). |
| Grouping by the wrong column | The query may combine records into unintended groups. | Check the GROUP BY columns against the reporting requirement. |
| Filtering grouped counts with WHERE | WHERE filters rows before aggregation. | Use HAVING for conditions on aggregate results. |
| Ignoring empty strings | COUNT(column) treats empty strings as non-NULL values. | Use TRIM() and a suitable condition if empty text should be excluded. |
| Counting rows after an unexpected JOIN multiplication | One source record may match multiple rows in another table. | Review the JOIN relationship and count the correct key or distinct entity. |
If a query returns an unexpected count, check the following:
Project Objective: Use COUNT() to create an employee summary report showing total employees, employees with recorded emails, unique departments, department-wise counts, and missing email records.
SELECT COUNT(*) AS total_employees
FROM employees;SELECT COUNT(email) AS employees_with_email
FROM employees;SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;SELECT COUNT(*) AS employees_without_email
FROM employees
WHERE email IS NULL;SELECT
COUNT(*) AS total_employees,
COUNT(email) AS employees_with_email,
COUNT(*) - COUNT(email) AS employees_without_email,
COUNT(DISTINCT department) AS unique_departments,
COUNT(DISTINCT city) AS unique_cities
FROM employees;This report summarizes the employee table in one row. The subtraction method for employees_without_email is valid here because COUNT(*) and COUNT(email) are calculated over the same set of rows, and email is the field being checked for NULL.
After completing this project, learners will be able to count records, compare non-NULL and distinct values, identify missing information, and prepare grouped reports using COUNT().
Use the employees table created in these notes to complete the following exercises:
Solution 1: Count all employees
SELECT COUNT(*) AS total_employees
FROM employees;Solution 2: Count employees with an email address
SELECT COUNT(email) AS employees_with_email
FROM employees;Solution 3: Count employees without an email address
SELECT COUNT(*) AS employees_without_email
FROM employees
WHERE email IS NULL;Solution 4: Count unique departments
SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;Solution 5: Count unique cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM employees;Solution 6: Count IT employees
SELECT COUNT(*) AS it_employees
FROM employees
WHERE department = 'IT';Solution 7: Count employees with salary greater than 45000
SELECT COUNT(*) AS employees_above_45000
FROM employees
WHERE salary > 45000;Solution 8: Count employees by department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;Solution 9: Count employees by city
SELECT city, COUNT(*) AS employee_count
FROM employees
GROUP BY city;Solution 10: Departments with more than one employee
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;Solution 11: Count employees earning at least 50000
SELECT COUNT(*) AS employees_earning_50000_or_more
FROM employees
WHERE salary >= 50000;Solution 12: Count non-empty email values
SELECT COUNT(*) AS valid_email_count
FROM employees
WHERE email IS NOT NULL
AND TRIM(email) <> '';Solution 13: Count unique department-and-city combinations
SELECT COUNT(DISTINCT department, city) AS unique_combinations
FROM employees;Solution 14: Count employees above average salary
SELECT COUNT(*) AS above_average_employees
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);Solution 15: Count orders by customer
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;COUNT() is an aggregate function that counts rows or non-NULL expression values, depending on the form used.
COUNT(*) counts all rows returned by the query, including rows where individual columns contain NULL.
It counts rows where the specified expression is not NULL.
It counts unique non-NULL values in a column.
COUNT(*) counts every row, whereas COUNT(email) counts only rows with non-NULL email values.
Yes. Zero is not NULL, so COUNT(column) includes zero values.
Yes. An empty string is not NULL. Use an additional condition to exclude empty or whitespace-only strings if necessary.
Use COUNT(*) with a WHERE clause that specifies the required condition.
Use COUNT() together with GROUP BY on the category column.
HAVING filters grouped results based on their aggregate counts.
Use COUNT(*) with a condition such as WHERE column_name IS NULL.
Use COUNT(column_name) or COUNT(*) with a WHERE condition such as column_name IS NOT NULL.
COUNT() returns 0 when no rows match the query or when there are no non-NULL expression values to count.
It counts matching records on the joined table and returns zero for unmatched rows when the counted primary key is NULL in the generated outer-join row.
For InnoDB, MySQL handles COUNT(1) and COUNT(*) in the same way, so COUNT(1) should not be assumed to be faster.
Conditional counting uses expressions such as CASE inside COUNT() to count rows that meet different conditions.
Yes. COUNT() can count rows returned by a subquery, including grouped results.
No. COUNT(DISTINCT column) counts unique non-NULL values.
The COUNT() function is an essential tool for counting records and analyzing data in MySQL. COUNT(*) counts rows, COUNT(column_name) counts non-NULL values, and COUNT(DISTINCT column_name) counts unique non-NULL values.
When combined with WHERE, GROUP BY, HAVING, ORDER BY, CASE expressions, and JOIN operations, COUNT() can answer a wide range of practical database questions. Understanding NULL values, duplicate records, and join behavior is important for obtaining accurate counts.
Practice the examples and exercises in these notes to build confidence in using COUNT() for employee reports, customer analysis, order summaries, data validation, and other real-world database tasks.
